package com.eight.cloud.excel.utils;


import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ZipUtil;
import cn.idev.excel.ExcelReader;
import cn.idev.excel.ExcelWriter;
import cn.idev.excel.FastExcel;
import cn.idev.excel.converters.longconverter.LongStringConverter;
import cn.idev.excel.read.builder.ExcelReaderBuilder;
import cn.idev.excel.read.metadata.ReadSheet;
import cn.idev.excel.support.ExcelTypeEnum;
import cn.idev.excel.write.builder.ExcelWriterBuilder;
import cn.idev.excel.write.metadata.WriteSheet;
import cn.idev.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.eight.cloud.core.global.exception.ServiceException;
import com.eight.cloud.excel.handler.SelectSheetWriteHandler;
import com.eight.cloud.excel.listener.DataListener;
import com.eight.cloud.excel.listener.ExcelListener;
import com.eight.cloud.excel.model.ImportExcelVo;
import com.eight.cloud.excel.model.Message;
import com.eight.cloud.excel.model.RelationFailRow;
import com.eight.cloud.excel.model.VerificationFailRow;
import com.eight.cloud.excel.service.AnalysisAfterService;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.CharEncoding;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.concurrent.Semaphore;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

/**
 * excel工具类
 *
 * @ClassName: ExcelUtils
 * @Author: TXC
 * @Date: 2025-08-06 15:20
 **/
@Slf4j
@Data
public class ExcelUtils<T> {
    /**
     * 最大允许并发导出的数量
     */
    private static final int MAX_EXPORT_COUNT = 100;
    private static final Semaphore exportSemaphore = new Semaphore(MAX_EXPORT_COUNT);
    private Class<T> head;
    public ExcelUtils(Class<T> head) {
        this.head = head;
    }

    /**
     * 获取用于导出 Excel 文件的输出流
     *
     * @param response HTTP 响应对象
     * @param fileName 导出文件名
     * @return ServletOutputStream 用于写入文件的输出流
     */
    @SneakyThrows
    public ServletOutputStream getServletOutputStream(HttpServletResponse response, String fileName) {
        return getResponse(response, fileName).getOutputStream();
    }

    /**
     * 配置响应对象以便进行文件下载
     *
     * @param response    HTTP 响应对象
     * @param fileName    导出文件名
     * @param contentType MIME 类型
     */
    private void configureResponse(HttpServletResponse response, String fileName, String contentType) {
        try {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
            response.setContentType(contentType);
            response.setCharacterEncoding(CharEncoding.UTF_8);
            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
        } catch (Exception e) {
            log.error("文件名编码失败: {}", e.getMessage(), e);
        }
    }

    public HttpServletResponse getResponse(HttpServletResponse response, String fileName) {
        fileName = fileName + ".xlsx";
        configureResponse(response, fileName, "application/vnd.ms-excel");
        return response;
    }

    public HttpServletResponse getResponseZip(HttpServletResponse response, String fileName) {
        fileName = fileName + ".zip";
        configureResponse(response, fileName, "application/zip");
        return response;
    }


    public List<T> importExcel(MultipartFile file) throws IOException {
        return FastExcel.read(file.getInputStream(), head, null).excelType(ExcelTypeEnum.XLSX)
                // 不要自动关闭，交给 Servlet 自己处理
                .autoCloseStream(false)
                .doReadAllSync();
    }

    /**
     * 从输入流解析 Excel 文件，并将数据映射到指定类型的对象列表
     *
     * @param inputStream Excel 文件输入流
     * @return 包含解析结果的 ReadVO 对象
     * @throws ServiceException 如果输入流为 null
     */
    public ImportExcelVo<T> importExcel(InputStream inputStream) {
        ImportExcelVo<T> result = new ImportExcelVo<>();
        if (inputStream == null) {
            throw new ServiceException("解析出错了，文件流是null");
        }
        DataListener<T> listener = new DataListener<>();
        try {
            FastExcel.read(inputStream, head, listener).excelType(ExcelTypeEnum.XLSX).sheet().doRead();
        } catch (Exception e) {
            log.error("读取Excel文件出错: {}", e.getMessage(), e);
            throw new ServiceException("读取Excel文件出错，请检查文件格式或内容");
        }
        result.setRows(listener.getRows());
        result.setConvertFails(listener.getConvertFails());
        result.setVerifyFails(listener.getVerifyFails());
        return result;
    }

    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @return Excel 数据 list
     */
    public List<T> readExcelData(MultipartFile excel, Integer headRowNumber) {
        ExcelListener<T> excelListener = new ExcelListener<>(null);
        ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
        if (readerBuilder == null) {
            return null;
        }
        if (headRowNumber == null) {
            headRowNumber = 1;
        }
        readerBuilder.head(head).headRowNumber(headRowNumber).doReadAll();
        return excelListener.getData();
    }


    /**
     * 读取 Excel(多个 sheet)
     *
     * @param excel    文件
     * @return Excel 数据 list
     */
    public List<T> importExcel(MultipartFile excel, AnalysisAfterService analysisAfterService) {
        ExcelListener<T> excelListener = new ExcelListener<>(analysisAfterService);
        ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
        if (readerBuilder == null) {
            return null;
        }
        readerBuilder.head(head).doReadAll();
        return excelListener.getData();
    }

    /**
     * 读取某个 sheet 的 Excel
     *
     * @param excel       文件
     * @param sheetNo     sheet 的序号 从1开始
     * @param headLineNum 表头行数，默认为1
     * @return Excel 数据 list
     */
    public List<T> importExcel(MultipartFile excel, AnalysisAfterService analysisAfterService,
                                          int sheetNo, Integer headLineNum) {
        ExcelListener<T> excelListener = new ExcelListener<>(analysisAfterService);
        ExcelReaderBuilder readerBuilder = getReader(excel, excelListener);
        if (readerBuilder == null) {
            return null;
        }
        ExcelReader reader = readerBuilder.headRowNumber(headLineNum).build();
        ReadSheet readSheet = FastExcel.readSheet(sheetNo).head(head).build();
        reader.read(readSheet);
        return excelListener.getData();
    }

    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener 监听器
     */
    private ExcelReaderBuilder getReader(MultipartFile excel, ExcelListener<?> excelListener) {
        String filename = excel.getOriginalFilename();
        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new ServiceException("文件格式错误！");
        }
        InputStream inputStream;
        try {
            inputStream = new BufferedInputStream(excel.getInputStream());
            return FastExcel.read(inputStream, excelListener).excelType(ExcelTypeEnum.XLSX);
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        }
        return null;
    }


    /**
     * 写入Excel（自定义列导出 Excel 文件，并根据第一条记录渲染表头）
     *
     * @param dataRowList 数据记录列表，基于第一条记录渲染表头
     * @param sheetName   Sheet 名称
     * @param response    HTTP 响应对象
     * @param fileName    文件名
     */
    @SneakyThrows
    public void exportExcel(List<LinkedHashMap<String, Object>> dataRowList, String sheetName,
                             HttpServletResponse response, String fileName) {
        List<List<?>> dataList = ListUtil.toList();
        List<List<String>> headers = ListUtil.toList();
        if (CollUtil.isNotEmpty(dataRowList)) {
            List<String> headerKeys = ListUtil.toList(dataRowList.get(0).keySet());
            for (String key : headerKeys) {
                headers.add(ListUtil.toList(key));
            }
            for (LinkedHashMap<String, Object> row : dataRowList) {
                dataList.add(ListUtil.toList(row.values()));
            }
        }
        exportExcel(dataList, headers, sheetName, response, fileName);
    }

    /**
     * 写入Excel（自定义列导出 Excel 文件）
     *
     * @param dataList  数据列表
     * @param headers   表头列表
     * @param sheetName Sheet 名称
     * @param response  HTTP 响应对象
     * @param fileName  文件名
     */
    @SneakyThrows
    public void exportExcel(List<List<?>> dataList, List<List<String>> headers, String sheetName,
                             HttpServletResponse response, String fileName) {
        try {
            Assert.notEmpty(dataList, "dataList must not be null");
            Assert.notEmpty(headers, "headers must not be null");
            Assert.hasText(sheetName, "sheetName must not be null");
            Assert.hasText(fileName, "fileName must not be null");
            writeSemaphore();
            ExcelWriterBuilder writerBuilder = FastExcel.write(getResponse(response, fileName).getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(false)
                    .registerConverter(new LongStringConverter())
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
            writerBuilder.head(headers);
            writerBuilder.sheet(sheetName).doWrite(dataList);
            writerBuilder.autoCloseStream(true);
        } finally {
            exportSemaphore.release();
        }
    }

    /**
     * 写入Excel（HttpServletResponse）
     *
     * @param response  HTTP 响应对象
     * @param filename  文件名
     * @param sheetName Sheet 名称
     * @param data      数据列表
     */
    @SneakyThrows
    public void exportExcel(HttpServletResponse response, String filename, String sheetName, List<T> data) {
        try (ServletOutputStream outputStream = getServletOutputStream(response, filename)) {
            exportExcel(outputStream, sheetName, data);
        } catch (IOException e) {
            log.error("写入Excel文件出错: {}", e.getMessage(), e);
            throw new ServiceException("写入Excel文件出错");
        }
    }

    /**
     * 写入Excel（HttpServletResponse）
     *
     * @param response  HTTP 响应对象
     * @param sheetName Sheet 名称(文件名和sheet名称一致)
     * @param data      数据列表
     */
    @SneakyThrows
    public void exportExcel(HttpServletResponse response, String sheetName,
                            List<T> data) {
        try (ServletOutputStream outputStream = getServletOutputStream(response, sheetName)) {
            exportExcel(outputStream, sheetName, data);
        } catch (IOException e) {
            log.error("写入Excel文件出错: {}", e.getMessage(), e);
            throw new ServiceException("写入Excel文件出错");
        }
    }

    /**
     * 写入Excel（OutputStream）
     *
     * @param outputStream 输出流
     * @param sheetName    Sheet 名称
     * @param data         数据列表
     */
    public void exportExcel(OutputStream outputStream, String sheetName, List<T> data) {
        try {
            Assert.notNull(outputStream, "outputStream must not be null");
            Assert.hasText(sheetName, "sheetName must not be null");
            Assert.notNull(head, "head must not be null");
            Assert.notEmpty(data, "data must not be null");
            writeSemaphore();
            FastExcel.write(outputStream, head)
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(false)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerConverter(new LongStringConverter())
                    .registerWriteHandler(new SelectSheetWriteHandler(head))
                    .sheet(sheetName).doWrite(data);
        } finally {
            exportSemaphore.release();
        }
    }

    /**
     * 写入Excel（无模型）
     *
     * @param response HTTP 响应对象
     * @param dataList 表数据列表
     * @param head     表头
     * @param fileName 文件名
     */
    public void exportExcel(HttpServletResponse response, List<?> dataList, List<String> head, String fileName) {
        try (ServletOutputStream outputStream = getServletOutputStream(response, fileName)) {
            exportExcel(outputStream, fileName, head, dataList);
        } catch (IOException e) {
            log.error("写入Excel文件出错: {}", e.getMessage(), e);
            throw new ServiceException("写入Excel文件出错");
        }
    }

    /**
     * 写入Excel（无模型）
     *
     * @param outputStream 输出流
     * @param sheetName    Sheet 名称
     * @param headList     表头名称列表
     * @param lineList     数据行列表
     */
    public void exportExcel(OutputStream outputStream, String sheetName, List<String> headList, List<?> lineList) {
        try {
            Assert.notNull(outputStream, "outputStream must not be null");
            Assert.hasText(sheetName, "sheetName must not be null");
            Assert.notEmpty(headList, "headList must not be null");
            Assert.notEmpty(lineList, "lineList must not be null");
            writeSemaphore();

            List<List<String>> list = headList.stream().map(Collections::singletonList).collect(Collectors.toList());
            FastExcel.write(outputStream)
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(false)
                    .registerConverter(new LongStringConverter())
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .head(list).sheet(sheetName).doWrite(lineList);
        } finally {
            exportSemaphore.release();
        }
    }


    /**
     * 检查和获取写入文件的许可
     * 如果达到最大并发量，则抛出异常
     */
    private void writeSemaphore() {
        if (!exportSemaphore.tryAcquire()) {
            throw new ServiceException("导出请求过多，请稍后再试");
        }
        log.info("目前Excel导出队列长度：{}，剩余可用许可：{}", MAX_EXPORT_COUNT - exportSemaphore.availablePermits(),
                exportSemaphore.availablePermits());
    }

    /**
     * 写入Excel（压缩zip）
     *
     * @param response  HTTP 响应对象
     * @param filename  文件名 xxx.zip
     * @param sheetName Sheet 名称
     * @param data      数据列表
     */
    @SneakyThrows
    public void writeZip(HttpServletResponse response, String filename, String sheetName,
                                    List<T> data) {
        Assert.notNull(response, "response must not be null");
        Assert.hasText(filename, "filename must not be null");
        Assert.hasText(sheetName, "sheetName must not be null");
        Assert.notNull(head, "head must not be null");
        Assert.notEmpty(data, "data must not be null");
        Map<String, InputStream> inputStreamMap = handleZipData(sheetName, data);

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        // 压缩文件
        ZipUtil.zip(outputStream, inputStreamMap.keySet().toArray(new String[0]),
                inputStreamMap.values().toArray(new InputStream[0]));
        // 输出附件
        IoUtil.write(getResponseZip(response, filename).getOutputStream(), false, outputStream.toByteArray());
    }

    /**
     * 写入Excel（压缩zip）
     *
     * @param file      文件 xxx.zip
     * @param sheetName Sheet 名称
     * @param data      数据列表
     */
    @SneakyThrows
    public void writeZip(File file, String sheetName, List<T> data) {
        Assert.notNull(file, "file must not be null");
        Assert.hasText(sheetName, "sheetName must not be null");
        Assert.notNull(head, "head must not be null");
        Assert.notEmpty(data, "data must not be null");
        Map<String, InputStream> inputStreamMap = handleZipData(sheetName, data);
        // 压缩文件
        ZipUtil.zip(file, inputStreamMap.keySet().toArray(new String[0]),
                inputStreamMap.values().toArray(new InputStream[0]));
    }

    /**
     * 处理压缩数据列表
     *
     * @param sheetName Sheet 名称
     * @param data      数据列表
     */
    private Map<String, InputStream> handleZipData(String sheetName, List<T> data) {
        AtomicInteger count = new AtomicInteger(0);
        Map<String, InputStream> inputStreamMap = MapUtil.newHashMap();
        ListUtil.partition(data, 10000).forEach(o -> {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            exportExcel(out, sheetName, o);
            inputStreamMap.put(String.format("file-%s.xlsx", count.getAndIncrement()), IoUtil.toStream(out));
        });
        return inputStreamMap;
    }

    /**
     * 多Sheet导出，支持模型写入
     *
     * @param response   HTTP 响应对象
     * @param fileName   文件名
     * @param sheetNames Sheet 名称列表
     * @param dataList   数据列表
     */
    @SneakyThrows
    public void writeWithModelByMultipleSheetNames(HttpServletResponse response,
                                                          String fileName,
                                                          List<String> sheetNames,
                                                          List<List<?>> dataList) {
        try {
            writeSemaphore();
            if (CollUtil.isEmpty(sheetNames) || CollUtil.isEmpty(dataList) || sheetNames.size() != dataList.size()) {
                throw new ServiceException("缺少必要参数");
            }
            ExcelWriter writer = FastExcel.write(getResponse(response, fileName).getOutputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .autoCloseStream(false)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerConverter(new LongStringConverter()).build();
            for (int i = 0; i < sheetNames.size(); i++) {
                if (CollUtil.isEmpty(dataList.get(i))) {
                    continue;
                }
                WriteSheet sheet = FastExcel.writerSheet(i, sheetNames.get(i))
                        .head(dataList.get(i).get(0).getClass())
                        .build();
                writer.write(dataList.get(i), sheet);
            }
            writer.finish();
        } finally {
            exportSemaphore.release();
        }
    }

    /**
     * 导出失败的数据列表
     *
     * @param response   HTTP 响应对象
     * @param sheetNames Sheet 名称列表
     * @param dataList   失败数据列表
     */
    @SneakyThrows
    private void writeFailFileSheetNames(HttpServletResponse response,
                                                List<String> sheetNames,
                                                List<List<?>> dataList) {
        if (CollUtil.isEmpty(sheetNames) || CollUtil.isEmpty(dataList) || sheetNames.size() != dataList.size()) {
            throw new ServiceException("缺少必要参数");
        }
        ExcelWriter writer = FastExcel.write(getResponse(response, "导入失败列表").getOutputStream())
                .excelType(ExcelTypeEnum.XLSX)
                .autoCloseStream(false)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerConverter(new LongStringConverter()).build();
        for (int i = 0; i < sheetNames.size(); i++) {
            List<?> data = dataList.get(i);
            if (CollUtil.isEmpty(data)) {
                continue;
            }
            WriteSheet sheet = FastExcel.writerSheet(i, sheetNames.get(i))
                    .head(data.get(0) instanceof Message ? Message.class : head)
                    .build();
            writer.write(data, sheet);
        }
        writer.finish();
    }

    /**
     * 导出失败的文件
     *
     * @param response         HTTP 响应对象
     * @param read             包含导入结果的 ReadVO 对象
     * @param relationFailRows 关联失败的行数据
     */
    public void exportImportFailFile(HttpServletResponse response,
                                                ImportExcelVo<T> read,
                                                List<RelationFailRow<T>> relationFailRows) {
        List<List<?>> failDataList = new ArrayList<>();
        List<String> sheetNames = new ArrayList<>();
        if (CollUtil.isNotEmpty(relationFailRows)) {
            failDataList.add(relationFailRows.stream().map(RelationFailRow::getRow).collect(Collectors.toList()));
            sheetNames.add("关联失败数据");

            failDataList.add(relationFailRows.stream().map(RelationFailRow::getMessage).collect(Collectors.toList()));
            sheetNames.add("关联失败提示");
        }

        if (CollUtil.isNotEmpty(read.getVerifyFails())) {
            failDataList.add(read.getVerifyFails().stream().map(VerificationFailRow::getRow).collect(Collectors.toList()));
            sheetNames.add("校验失败数据");

            failDataList.add(read.getVerifyFails().stream().map(VerificationFailRow::getMessage).collect(Collectors.toList()));
            sheetNames.add("校验失败提示");
        }

        if (CollUtil.isNotEmpty(read.getConvertFails())) {
            failDataList.add(read.getConvertFails().stream().map(VerificationFailRow::getRow).collect(Collectors.toList()));
            sheetNames.add("转换失败数据");

            failDataList.add(read.getConvertFails().stream().map(VerificationFailRow::getMessage).collect(Collectors.toList()));
            sheetNames.add("转换失败提示");
        }
        if (CollUtil.isNotEmpty(failDataList)) {
            writeFailFileSheetNames(response, sheetNames, failDataList);
        }
    }

}
